<?php
class media_draft_model extends CI_Model {
	public function __construct() {
		parent::__construct ();
		$this->db = $this->load->database ( 'default', true );
	}
	public function save_media_draft($media) {
		$this->db->insert ( "media_draft", $media );
		return $this->db->insert_id ();
	}
	public function update_media_draft($media, $media_draft_id) {
		$this->db->where ( "media_draft_id", $media_draft_id );
		$this->db->update ( "media_draft", $media );
		return $this->db->insert_id ();
	}
	public function save_pic($pic, $pic_thumb, $media_draft_id) {
		$data = array (
				"pic" => $pic,
				"pic_thumb" => $pic_thumb,
				"media_draft_id" => $media_draft_id 
		);
		$this->db->insert ( "media_draft_pic", $data );
	}
	public function delete_draft_pic($pic_id) {
		$pic_id = $this->db->escape_str ( $pic_id );
		$sql = "delete from media_draft_pic where pic_id ='$pic_id'";
		$this->db->query ( $sql );
	}
	public function delete_media_draft($media_draft_id) {
		$sql = "delete from media_draft where media_draft_id ='$media_draft_id'";
		$this->db->query ( $sql );
	}
	public function delete_pic($media_draft_id) {
		$sql = "delete from media_draft_pic where media_draft_id ='$media_draft_id'";
		$this->db->query ( $sql );
	}
	public function query_my_media_draft_num($user_name, $user_id, $search) {
		if ($user_id == 1) {
			$sql = "select count(media_draft_id) as num from media_draft
					where  be_delete='0'";
		} else {
			$sql = "select count(media_draft_id) as num 
					from media_draft
						inner join adm_user_region as aur on media_draft.province=aur.region_id
					where  be_delete='0'
					and user_id ='$user_id'
					and (create_by = '$user_name' or  modify_by = '$user_name' or create_by is null )";
		}
		
		$search_path = "";
		if ($search ['region']) {
			$region = $this->db->escape ( $search ['region'] );
			$search_path = " and  region =$region";
		} elseif ($search ['city']) {
			$city = $this->db->escape ( $search ['city'] );
			$search_path = " and  city =$city";
		} elseif ($search ['province']) {
			$province = $this->db->escape ( $search ['province'] );
			$search_path = " and  province =$province";
		}
		if ($search ['media_type'] != "") {
			$media_type = $this->db->escape ( $search ['media_type'] );
			$search_path .= " and media_type = $media_type";
		}
		if ($search ['media_type_id'] != "") {
			$province = $this->db->escape ( $search ['media_type_id'] );
			$search_path .= " and media_type_id = $media_type_id";
		}
		if ($search ['keyword'] != "") {
			$keyword = $this->db->escape_like_str ( $search ['keyword'] );
			$search_path .= " and location like '%$keyword%'";
		}
		$sql = $sql . $search_path;
		
		$query = $this->db->query ( $sql );
		if ($query) {
			$row = $query->row_array ();
			return $row ['num'];
		}
	}
	public function query_my_media_draft_list($user_name, $user_id, $start, $limit, $search) {
		if ($user_id == 1) {
			$sql = "select
				media_draft_id,
				media_draft_type,
				location,
				media_type,
				media_status
			from media_draft as md
			where 
				be_delete='0' ";
		} else {
			$sql = "select
					media_draft_id,
					media_draft_type,
					location,
					media_type,
					media_status
				from media_draft as md
				inner join adm_user_region as aur on md.province=aur.region_id
				where 
					be_delete='0'
					and user_id ='$user_id'
					and (create_by = '$user_name' or  modify_by = '$user_name' or create_by is null ) ";
		}
		
		$search_path = "";
		if ($search ['region']) {
			$region = $this->db->escape ( $search ['region'] );
			$search_path = " and  region =$region";
		} elseif ($search ['city']) {
			$city = $this->db->escape ( $search ['city'] );
			$search_path = " and  city =$city";
		} elseif ($search ['province']) {
			$province = $this->db->escape ( $search ['province'] );
			$search_path = " and  province =$province";
		}
		if ($search ['media_type'] != "") {
			$media_type = $this->db->escape ( $search ['media_type'] );
			$search_path .= " and media_type = $media_type";
		}
		if ($search ['media_type_id'] != "") {
			$province = $this->db->escape ( $search ['media_type_id'] );
			$search_path .= " and media_type_id = $media_type_id";
		}
		if ($search ['keyword'] != "") {
			$keyword = $this->db->escape_like_str ( $search ['keyword'] );
			$search_path .= " and location like '%$keyword%'";
		}
		$sql = $sql . $search_path;
		$sql = $sql . " order by media_draft_id desc limit $start,$limit";
		
		$query = $this->db->query ( $sql );
		if ($query) {
			return $query->result_array ();
		}
	}
	public function query_media_draft($media_draft_id) {
		$sql = "select * from media_draft where media_draft_id ='$media_draft_id' limit 1 ";
		$query = $this->db->query ( $sql );
		if ($query) {
			return $query->row_array ();
		}
	}
	public function query_media_type($media_draft_id) {
	}
	public function query_media_draft_detail($media_draft_id) {
		$sql = "select
				m.*,
					r1.region_name as province_name,
					r2.region_name as city_name,
					r3.region_name as region_name,
					r3.region_type,
					sc.crafts_name,
					scc.corner_case_name,
					smt.media_type_name,
					sma.media_area_name,
					vv.company_name
				from
					media_draft as m
					inner join sys_region as r1 on m.province =r1.region_id
					left join sys_region as r2 on m.city =r2.region_id
					left join sys_region as r3 on m.region =r3.region_id
					left join ven_vendor as vv on m.vendor =vv.vendor_id
					left join sys_crafts as sc on m.crafts =sc.crafts_id
					left join sys_corner_case  as scc on m.corner_case =scc.corner_case_id
					left join sys_media_type as smt on m.media_type_id =smt.media_type_id
					left join sys_media_area as sma on m.media_area =sma.media_area_id
				where
					m.media_draft_id ='$media_draft_id'
					limit 1";
		
		$query = $this->db->query ( $sql );
		if ($query) {
			return $query->row_array ();
		}
	}
	public function query_media_draft_pic_list($media_draft_id) {
		$sql = "select * from media_draft_pic where media_draft_id ='$media_draft_id'";
		$query = $this->db->query ( $sql );
		if ($query) {
			return $query->result_array ();
		}
	}
	public function submit_check_draft($media_draft_id) {
		$sql = "	update
					media_draft
				set
					media_status ='10'
				where
					media_draft_id ='$media_draft_id'
					and media_status ='0'";
		$this->db->query ( $sql );
		return $this->db->affected_rows ();
	}
	public function query_check_media_draft_list($user_id, $start, $limit, $search) {
		if ($user_id == 1) {
			$sql = "select
						media_draft_id,
						media_draft_type,
						location,
						media_type,
						media_status
					from media_draft as md
					where 
						be_delete='0'
						and media_status='10'
					";
		} else {
			$sql = "select
					media_draft_id,
					media_draft_type,
					location,
					media_type,
					media_status
				from media_draft as md
				inner join adm_user_region as aur on md.province=aur.region_id
				where 
					be_delete='0'
					and user_id ='$user_id'
					and media_status='10'
				";
		}
		
		$search_path = "";
		if ($search ['region']) {
			$region = $this->db->escape ( $search ['region'] );
			$search_path = " and  region =$region";
		} elseif ($search ['city']) {
			$city = $this->db->escape ( $search ['city'] );
			$search_path = " and  city =$city";
		} elseif ($search ['province']) {
			$province = $this->db->escape ( $search ['province'] );
			$search_path = " and  province =$province";
		}
		if ($search ['media_type'] != "") {
			$media_type = $this->db->escape ( $search ['media_type'] );
			$search_path .= " and media_type = $media_type";
		}
		if ($search ['media_type_id'] != "") {
			$province = $this->db->escape ( $search ['media_type_id'] );
			$search_path .= " and media_type_id = $media_type_id";
		}
		if ($search ['keyword'] != "") {
			$keyword = $this->db->escape_like_str ( $search ['keyword'] );
			$search_path .= " and location like '%$keyword%'";
		}
		$sql = $sql . $search_path;
		$sql = $sql . " order by media_draft_id desc limit $start,$limit";
		
		$query = $this->db->query ( $sql );
		if ($query) {
			return $query->result_array ();
		}
	}
	public function query_check_media_draft_num($user_id, $search) {
		if ($user_id == 1) {
			$sql = "select count(media_draft_id) as num from media_draft where  be_delete='0' and media_status='10' ";
		} else {
			$sql = "select count(media_draft_id) as num from media_draft 
					
					inner join adm_user_region as aur on media_draft.province=aur.region_id
					where  be_delete='0'
							and user_id ='$user_id' 
							and media_status='10' ";
		}
		$search_path = "";
		if ($search ['region']) {
			$region = $this->db->escape ( $search ['region'] );
			$search_path = " and  region =$region";
		} elseif ($search ['city']) {
			$city = $this->db->escape ( $search ['city'] );
			$search_path = " and  city =$city";
		} elseif ($search ['province']) {
			$province = $this->db->escape ( $search ['province'] );
			$search_path = " and  province =$province";
		}
		if ($search ['media_type'] != "") {
			$media_type = $this->db->escape ( $search ['media_type'] );
			$search_path .= " and media_type = $media_type";
		}
		if ($search ['media_type_id'] != "") {
			$province = $this->db->escape ( $search ['media_type_id'] );
			$search_path .= " and media_type_id = $media_type_id";
		}
		if ($search ['keyword'] != "") {
			$keyword = $this->db->escape_like_str ( $search ['keyword'] );
			$search_path .= " and location like '%$keyword%'";
		}
		$sql = $sql . $search_path;
		
		$query = $this->db->query ( $sql );
		if ($query) {
			$row = $query->row_array ();
			return $row ['num'];
		}
	}
	public function set_delete_media_draft($media_draft_id) {
		$sql = "	update
						media_draft
						set
						be_delete ='1',
						media_status ='30'
					where
						media_draft_id ='$media_draft_id'
						and media_status ='10'";
		$this->db->query ( $sql );
		return $this->db->affected_rows ();
	}
	public function not_allow_draft($media_draft_id) {
		$sql = "	update
					media_draft
				set
					media_status ='20'
				where
					media_draft_id ='$media_draft_id'
					and media_status ='10'";
		$this->db->query ( $sql );
		return $this->db->affected_rows ();
	}
	public function mobile_query_my_media_draft_list($user_name, $user_id, $sort, $start, $limit) {
		$sort = "media_status";
		if ($user_id == 1) {
			$sql = "select * from (select
						media_draft_id as media_id,
						location,
						media_type ,
						media_status,
						province,
						city,
						region,
						r1.region_name as province_name,
						r2.region_name as city_name,
						r3.region_name as region_name,
						md.modify_time
					from media_draft as md
						inner join sys_region as r1 on md.province =r1.region_id
						left join sys_region as r2 on md.city =r2.region_id
						left join sys_region as r3 on md.region =r3.region_id
					where 
						be_delete='0'
					UNION ALL 
						select
						media_id,
						location,
						media_type,
						media_status,
						province,
						city,
						region,
						r1.region_name as province_name,
						r2.region_name as city_name,
						r3.region_name as region_name,
						media.modify_time
					from 	
						media
						inner join sys_region as r1 on media.province =r1.region_id
						left join sys_region as r2 on media.city =r2.region_id
						left join sys_region as r3 on media.region =r3.region_id
						) as t
					order by $sort desc,t.modify_time desc";
		} else {
			$sql = "select * from (select
						media_draft_id as media_id,
						location,
						media_type ,
						media_status,
						province,
						city,
						region,
						r1.region_name as province_name,
						r2.region_name as city_name,
						r3.region_name as region_name,
						md.modify_time as modify_time
					from media_draft as md
						inner join adm_user_region as aur on md.province=aur.region_id
						inner join sys_region as r1 on md.province =r1.region_id
						left join sys_region as r2 on md.city =r2.region_id
						left join sys_region as r3 on md.region =r3.region_id
					where 
						be_delete='0'
						and user_id ='$user_id'
						and (create_by = '$user_name' or  modify_by = '$user_name') 
				UNION ALL 
					select
						media_id,
						location,
						media_type,
						media_status,
						media.province,
						media.city,
						media.region,
						r1.region_name as province_name,
						r2.region_name as city_name,
						r3.region_name as region_name,
						media.modify_time
					from 	
						media inner join adm_user_region as aur on media.province=aur.region_id
						inner join sys_region as r1 on media.province =r1.region_id
						left join sys_region as r2 on media.city =r2.region_id
						left join sys_region as r3 on media.region =r3.region_id
					where aur.user_id ='$user_id' ) as t
					order by $sort desc,t.modify_time desc";
		}
		
		$query = $this->db->query ( $sql );
		if ($query) {
			return $query->result_array ();
		}
	}
	public function mobile_search_my_media_draft_list($user_name, $user_id, $sort, $keyword) {
		if ($sort == "") {
			$sort = "media_status";
		} else {
			$sort = "province";
		}
		if ($user_id == 1) {
			$sql = "select
						media_draft_id as media_id,
						location,
						media_type ,
						media_status,
						province,
						city,
						region,
						r1.region_name as province_name,
						r2.region_name as city_name,
						r3.region_name as region_name
					from media_draft as md
						inner join sys_region as r1 on md.province =r1.region_id
						left join sys_region as r2 on md.city =r2.region_id
						left join sys_region as r3 on md.region =r3.region_id
					where 
						be_delete='0' and (media_draft_id ='$keyword' or location like '%$keyword%')
					UNION ALL 
						select
						media_id,
						location,
						media_type,
						media_status,
						province,
						city,
						region,
						r1.region_name as province_name,
						r2.region_name as city_name,
						r3.region_name as region_name
					from 	
						media
						inner join sys_region as r1 on media.province =r1.region_id
						left join sys_region as r2 on media.city =r2.region_id
						left join sys_region as r3 on media.region =r3.region_id
					where 
						 (media_id ='$keyword' or location like '%$keyword%')
					order by $sort desc,media_id desc";
		} else {
			$sql = "select
						media_draft_id as media_id,
						location,
						media_type ,
						media_status,
						province,
						city,
						region,
						r1.region_name as province_name,
						r2.region_name as city_name,
						r3.region_name as region_name
					from media_draft as md
						inner join adm_user_region as aur on md.province=aur.region_id
						inner join sys_region as r1 on md.province =r1.region_id
						left join sys_region as r2 on md.city =r2.region_id
						left join sys_region as r3 on md.region =r3.region_id
					where 
						be_delete='0'
						and (media_draft_id ='$keyword' or location like '%$keyword%') 
						and user_id ='$user_id'
						and (create_by = '$user_name' or  modify_by = '$user_name') 
				UNION ALL 
					select
						media_id,
						location,
						media_type,
						media_status,
						media.province,
						media.city,
						media.region,
						r1.region_name as province_name,
						r2.region_name as city_name,
						r3.region_name as region_name
					from 	
						media inner join adm_user_region as aur on media.province=aur.region_id
						inner join sys_region as r1 on media.province =r1.region_id
						left join sys_region as r2 on media.city =r2.region_id
						left join sys_region as r3 on media.region =r3.region_id
					where aur.user_id ='$user_id'
					and  (media_id ='$keyword' or location like '%$keyword%')
					order by $sort desc,media_id desc";
		}
		$query = $this->db->query ( $sql );
		if ($query) {
			return $query->result_array ();
		}
	}
	public function query_media_draft_unique_id($media_draft_id) {
		$sql = "select unique_id from  media_draft where media_draft_id ='$media_draft_id'";
		$query = $this->db->query ( $sql );
		if ($query) {
			$row = $query->row_array ();
			return $row ['unique_id'];
		}
	}
}